package com.example.crm.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.crm.entity.Opportunity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * 销售机会Mapper接口
 */
@Mapper
public interface OpportunityMapper extends BaseMapper<Opportunity> {
    
    /**
     * 根据客户ID获取销售机会列表
     */
    @Select("SELECT * FROM crm_opportunity WHERE customer_id = #{customerId} AND deleted = 0 ORDER BY create_time DESC")
    List<Opportunity> selectByCustomerId(@Param("customerId") Long customerId);
    
    /**
     * 获取销售漏斗数据
     */
    @Select("SELECT " +
            "COUNT(CASE WHEN stage = 1 THEN 1 END) AS stage1, " +
            "COUNT(CASE WHEN stage = 2 THEN 1 END) AS stage2, " +
            "COUNT(CASE WHEN stage = 3 THEN 1 END) AS stage3, " +
            "COUNT(CASE WHEN stage = 4 THEN 1 END) AS stage4, " +
            "COUNT(CASE WHEN stage = 5 THEN 1 END) AS stage5, " +
            "COUNT(CASE WHEN stage = 6 THEN 1 END) AS stage6 " +
            "FROM crm_opportunity WHERE deleted = 0")
    Map<String, Object> getSalesFunnelData();
    
    /**
     * 获取各阶段销售机会数量
     */
    @Select("SELECT stage, COUNT(*) as count " +
            "FROM crm_opportunity " +
            "WHERE deleted = 0 " +
            "GROUP BY stage")
    List<Map<String, Object>> countGroupByStage();
    
    /**
     * 统计本月新增销售机会数
     */
    @Select("SELECT COUNT(*) FROM crm_opportunity " +
            "WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') " +
            "AND deleted = 0")
    Long countNewOpportunitiesThisMonth();
    
    /**
     * 统计本月新增销售机会金额
     */
    @Select("SELECT IFNULL(SUM(expected_amount), 0) FROM crm_opportunity " +
            "WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') " +
            "AND deleted = 0")
    BigDecimal sumNewOpportunityAmountThisMonth();
} 